﻿<%@ WebHandler Language="C#" Class="ShowListLinkage" %>
using System;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data;
using Chenxin.Lib.Utility;
using Chenxin.Lib.Business;
using Chenxin.Lib.Business.BLL;
using System.Data.OleDb;

public class ShowListLinkage : AjaxHandlerBase
{
    public ShowListLinkage()
    {
        //导入
        htTypePermit.Add("uploaddata", Constants.Permit_Linkage_InputData);
        //导出
        htTypePermit.Add("export", Constants.Permit_Linkage_Export);
        //删除
        htTypePermit.Add("del", Constants.Permit_Linkage_DelAchievement);
        
    }
    override public void RequestProcess(HttpContext context)
    {
        try
        {
            context.Response.ContentType = "text/plain";
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";

            string strOp = context.Request["op"];
            string strRet = string.Empty;
            switch (strOp.ToLower())
            {
                //添加报备相关信息
                case "getlist":
                    GetList(context);
                    break;
                case "uploaddata":
                    UploadData(context);
                    break;
                case "geitemlist":
                    GeItemList(context);
                    break;
                case "del":
                    Del(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Constants.ExecResult.Error, ex.Message, ""));
        }
    }

    //删除设定
    private void Del(HttpContext context)
    {
        try
        {
            string listid = context.Request.Params["modelid"];
            // listid = "'" + listid + "'";
            if(listid.Length<1){
                context.Response.Write(string.Format("{{success:'0',msg:'没有要删除的数据！',data:''}}"));
                return;
            }
            listid = listid.Replace(",", "','");
            string strSql = string.Format("delete t_Linkage_Commision where RecordId in ('{0}') ;", listid);
            Chenxin.Lib.Business.DAL.t_Linkage_Commision.ExecSql(strSql);

            string text = CurrentUserInfo.UserName + "：删除一手业绩。";
            AddLog(text, BusinessType.BusinessTypeComboList()["OprType_LinkageDel"].ToString());
            
            context.Response.Write(string.Format("{{success:'1',msg:'删除成功',data:''}}"));
        }
        catch (Exception ex)
        {
            context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:''}}", ex.Message));
        }
    }
    
    //获取项目名称
    private void GeItemList(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        List<Chenxin.Lib.Business.Entity.t_Linkage_ItemInfo> itemlist = Chenxin.Lib.Business.DAL.t_Linkage_ItemInfo.GetListArray("(1=1)");
        foreach (Chenxin.Lib.Business.Entity.t_Linkage_ItemInfo info  in itemlist)
        {
            sb.Append("{");
            sb.AppendFormat("txt:'{0}',val:'{1}'", info.ItemsName, info.RecordId);
            sb.Append("},");
        }
        if (sb.Length > 0)
        {
            sb.Remove(sb.Length - 1, 1);
        }
        context.Response.Write(string.Format("{{success:'1',msg:'成功',data:{{items:[{0}]}}}}", sb.ToString()));
    }
    
    //查询
    private void GetList(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        if (CurrentUserInfo.ContainsPermit(Constants.Permit_LinkageSearch_All))
        {
            sb.Append(" (1=1) ");
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_LinkageSearch_City))
        {
            sb.AppendFormat(" DepartId in (select departid from v_Hr_DepartmentInfo where CityId='{0}')", CurrentUserInfo.CityId);
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_LinkageSearch_Company))
        {
            sb.AppendFormat(" DepartId in (select departid from v_Hr_DepartmentInfo where CompanyId='{0}')", CurrentUserInfo.CompanyId);
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_LinkageSearch_Depart))
        {
            sb.AppendFormat(" DepartId in (select DepartId from t_Business_EmpSetting where UserId='{0}' and SearchLinkage=1)", CurrentUserInfo.UserId);
        }
        else 
        {
            sb.AppendFormat(" UserId = '{0}'", CurrentUserInfo.UserId);
        }
        
        //查询5级
        Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
        Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
        Guid userId = (string.IsNullOrEmpty(context.Request["UserId"])) ? Guid.Empty : new Guid(context.Request["UserId"]);

        if (userId != Guid.Empty)
            sb.AppendFormat(" And UserId = '{0}'", userId);
        if (departs != Guid.Empty)
        {
            sb.AppendFormat(" And DepartId = '{0}'", departs);
        }
        else if (businessdistrict != Guid.Empty)
        {
            sb.AppendFormat(" and DepartId in (Select ChildId from v_Hr_Struct_DepartTreeRelation where ParentId ='{0}')",businessdistrict);
        }
        else if (company != Guid.Empty)
        {
            sb.AppendFormat(" and DepartId in (select departid from v_Hr_DepartmentInfo where CompanyId='{0}')", company);
        }
        else if (city != Guid.Empty)
        {
            sb.AppendFormat(" and  DepartId in (select departid from v_Hr_DepartmentInfo where CityId='{0}')", city);
        }
        else
        {
            sb.AppendFormat(" and DepartId in (select departid from v_Hr_DepartmentInfo where CompanyId='{0}')", CurrentUserInfo.CompanyId);
        }

        if (!string.IsNullOrEmpty(context.Request["SignTime"]))
        {
            sb.AppendFormat(" and AchievementDate between '{0}' and '{1}'", DateTime.Parse(context.Request["SignTime"].ToString()), DateTime.Parse(context.Request["SignTime"].ToString()).AddMonths(1).AddDays(-1));
        }
        if (!string.IsNullOrEmpty(context.Request["keyword"]))
        {
            sb.AppendFormat(" and (ItemName like '%{0}%')", context.Request["keyword"]);
        }

        //条件
        string strWhere = sb.ToString();
        //页码
        int strPageIndex = (string.IsNullOrEmpty(context.Request["pageindex"])) ? 1 : int.Parse(context.Request["pageindex"]) + 1;//页码
        //页显示行数
        int strPageSize = (string.IsNullOrEmpty(context.Request["pagesize"])) ? 30 : int.Parse(context.Request["pagesize"]);//页记录数
        string TableName = "dbo.t_Linkage_Commision";
        //排序
        string strOrderField = "BargainTime desc";
        if (!string.IsNullOrEmpty(context.Request["sort"]))
            strOrderField = context.Request["sort"];

        //根据sql执行查询
        DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex, strPageSize, strOrderField, TableName, strWhere, "*", "*", true);

        //*************获取总合计数*****************
        string strSql = "select sum(BargainTotal) as BargainTotal,sum(SignTotal) as SignTotal,sum(Achievement) as Achievement,sum(EmpCommision) as EmpCommision,sum(DZCommision) as DZCommision,sum(ZJCommision) as ZJCommision  from t_Linkage_Commision where " + sb.ToString();
        DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        System.Data.DataRow dr = ds.Tables[0].NewRow();
        dr["UserCode"] = "总合计";
        dr["BargainTotal"] = ds_count.Tables[0].Rows[0]["BargainTotal"];
        dr["SignTotal"] = ds_count.Tables[0].Rows[0]["SignTotal"];
        dr["Achievement"] = ds_count.Tables[0].Rows[0]["Achievement"];
        dr["EmpCommision"] = ds_count.Tables[0].Rows[0]["EmpCommision"];
        dr["DZCommision"] = ds_count.Tables[0].Rows[0]["DZCommision"];
        dr["ZJCommision"] = ds_count.Tables[0].Rows[0]["ZJCommision"];
        ds.Tables[0].Rows.Add(dr);
        //*****************************************

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[1].Rows[0][0], Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0]))); 
    }

    //导入一手联动数据
    private void UploadData(HttpContext context)
    {
        string filepath = "";
        try
        {
            HttpPostedFile file = context.Request.Files["FileData"];
            string localPath = System.Configuration.ConfigurationManager.AppSettings["FilePath"] + "File/";

            string savePath = context.Server.MapPath(localPath);
            string fileName = Guid.NewGuid().ToString("N") + file.FileName.Substring(file.FileName.LastIndexOf('.'));
            string subName = file.FileName.Substring(file.FileName.LastIndexOf('.') + 1);
            if (!("xls".Equals(subName) || "xlsx".Equals(subName)))
            {
                context.Response.Write("{success:0,msg:'文件格式不正确！',data:''}");
                return;
            }
            if (file != null)
            {
                if (!System.IO.Directory.Exists(savePath))
                {
                    System.IO.Directory.CreateDirectory(savePath);
                }

                file.SaveAs(savePath + fileName);
                filepath = savePath + fileName;
                if (adddata(localPath + fileName, context))
                    context.Response.Write(string.Format("{{success:1,msg:'导入数据成功！',data:{{filepath:'{0}'}}}}", localPath + fileName));
            }
            else
            {
                context.Response.Write("{success:0,msg:'文件上传失败！',data:''}");
            }
        }
        catch (Exception e)
        {
            context.Response.Write("{success:0,msg:'文件上传失败！',data:''}");
        }
        finally
        {
            if (System.IO.File.Exists(filepath))
            {
                System.IO.File.Delete(filepath);
            }
        }
    }

    //导入数据方法
    private bool adddata(string FileName, HttpContext context)
    {
        try
        {
            string adate = context.Request["AchievementDate"];
            string itemid = context.Request["ItemId"];  //项目ID
            string itemname = "";
            Chenxin.Lib.Business.Entity.t_Linkage_ItemInfo item = Chenxin.Lib.Business.DAL.t_Linkage_ItemInfo.GetModel(new Guid(itemid));
            if (item != null)
                itemname = item.ItemsName;
            else 
            {
                context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "项目不存在！导入失败！"));
                return false;
            }
            //转换成实体
            DataSet dsExcel = new DataSet();
            DataTable dt = new DataTable();
            string SheetName = "Sheet1";
            string strXls = FileName.Substring(FileName.LastIndexOf(".") + 1).ToLower();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(FileName) + ";Extended Properties=Excel 8.0";
            OleDbConnection oleConn = new OleDbConnection(strConn);
            oleConn.Open();
            OleDbCommand myOleDbCommand = new OleDbCommand("select * from [" + SheetName + "$]", oleConn);
            OleDbDataAdapter dataAda = new OleDbDataAdapter(myOleDbCommand);
            oleConn.Close();
            dsExcel = new DataSet();
            dataAda.Fill(dsExcel, "[" + SheetName + "$]");
            dt = dsExcel.Tables[0];

            StringBuilder strSql = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                if (string.IsNullOrEmpty(dt.Rows[i]["业务员"].ToString().Trim()) || string.IsNullOrEmpty(dt.Rows[i]["门店"].ToString().Trim()) || string.IsNullOrEmpty(dt.Rows[i]["员工工号"].ToString().Trim()))
                {
                    context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "业务员、门店、工号不能为空！导入失败！"));
                    return false;
                }
                else
                {
                    string code = dt.Rows[i]["员工工号"].ToString();
                    string comcode = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetComCode(CurrentUserInfo.CompanyId);
                    string tempcode = "000000" + code;
                    string usercode = comcode + tempcode.Substring(tempcode.Length - 6, 6);
                    Chenxin.Lib.Passport.Entity.Hr_UserInfo userinfo = Chenxin.Lib.Passport.DAL.Hr_UserInfo.GetEntityByCode(usercode);
                    if (userinfo == null)
                    {
                        context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "工号" + code + "不存在！导入失败！"));
                        return false;
                    }
                    if (userinfo.UserName != dt.Rows[i]["业务员"].ToString().Trim())
                    {
                        context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "工号" + code + "所对应姓名不匹配！导入失败！"));
                        return false;
                    }
                    if (!CheckDep(dt.Rows[i]["门店"].ToString().Trim()))
                    {
                        context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "工号" + code + "所属部门不存在！导入失败！"));
                        return false;
                    }
                    strSql.Append("insert into t_Linkage_Commision(RecordId,[UserId],[UserName],[UserCode],[DepartId],[DepartName],[ItemId],[ItemName],[CustomerName],[Ridgepole],[Floor],[Room]");
                    strSql.Append(",[Area],[BargainTime],[BargainUnit],[BargainTotal],[SignName],[SignTime],[SignTotal],[SupplyCommisionScale],[Achievement],[EmpCommisionScale],[EmpCommision]");
                    strSql.Append(",[DZCommisionScale],[DZCommision],[ZJCommisionScale],[ZJCommision],[Point],AchievementDate,OprCityId,OprCityName,OprCompanyId,OprCompanyName,OprEmployeeId,OprEmployee,OprTime) values(");
                    strSql.AppendFormat("'{0}',",Guid.NewGuid());  //RecordId
                    strSql.AppendFormat("'{0}',",userinfo.UserId);  //UserId
                    strSql.AppendFormat("'{0}',",userinfo.UserName);  //UserName
                    strSql.AppendFormat("'{0}',",userinfo.UserCode);  //UserCode
                    strSql.AppendFormat("'{0}',", Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetIdByDepName(dt.Rows[i]["门店"].ToString().Trim()));  //DepartId
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "门店", ""));  //DepartName
                    strSql.AppendFormat("'{0}',",itemid);  //ItemId
                    strSql.AppendFormat("'{0}',",itemname);  //ItemName
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "客户姓名", ""));  //CustomerName
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "幢号", ""));  //Ridgepole
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "楼号", ""));  //Floor
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "房号", ""));  //Room
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "面积", (float)0));  //Area
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "大定日期", DateTime.Now));  //BargainTime
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "成交单价", (float)0));  //BargainUnit   
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "成交总价", (float)0));  //BargainTotal
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "签约姓名", ""));  //SignName
                    strSql.AppendFormat("'{0}',", DatasetHelper.GetValue(dr, "签约日期", DateTime.Now));  //SignTime
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "签约总价", (float)0));  //SignTotal
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "代理佣金比例", (float)0));  //SupplyCommisionScale
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "一手业绩", (float)0));  //Achievement
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "业务员佣金比例", (float)0));  //EmpCommisionScale
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "业务员佣金", (float)0));  //EmpCommision
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "店长佣金比例", (float)0));  //DZCommisionScale
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "店长佣金", (float)0));  //DZCommision
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "区经理佣金比例", (float)0));  //ZJCommisionScale
                    strSql.AppendFormat("{0},", DatasetHelper.GetValue(dr, "区经理佣金", (float)0));  //ZJCommision
                    strSql.AppendFormat("{0},'{1}',", 10, adate);  //Point   ,业绩归属                 
                    strSql.AppendFormat("'{0}','{1}',", CurrentUserInfo.CityId,CurrentUserInfo.CityName);  //操作人城市   
                    strSql.AppendFormat("'{0}','{1}',", CurrentUserInfo.CompanyId,CurrentUserInfo.CompanyName);  //操作人公司   
                    strSql.AppendFormat("'{0}','{1}','{2}');\n", CurrentUserInfo.UserId,CurrentUserInfo.UserName,DateTime.Now);  //操作人   
                }                
            }
            if (Chenxin.Lib.Business.DAL.t_Linkage_Commision.ExecSql(strSql.ToString()))
            {
                string text = CurrentUserInfo.UserName + "：一手业绩导入。项目名称：" + itemname + ",业绩归属：" + adate;
                AddLog(text, BusinessType.BusinessTypeComboList()["OprType_LinkageInput"].ToString());
                
                return true;
            }
            else
            {
                context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "数据库执行出错！导入失败！"));
                return false;
            }
            
        }
        catch (Exception ex)
        {
            context.Response.Write(string.Format("{{success:'0',msg:'{0}',data:{{}}}}", "导出错误"+ex.Message+"！导入失败！"));
            return false;
        }
    }

    private bool CheckDep(string depname)
    {
        bool flag = true;
        string strSql = "select CompanyId from v_Hr_DepartmentInfo where departname='" + depname + "'";
        string companyid = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetScalar(strSql);
        if (companyid != CurrentUserInfo.CompanyId.ToString())
            flag = false;
        return flag;
    }
}